﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Serilog;
using TeBigCoreApi.Common;
using TeBigCoreApi.Models;
using TeBigCoreApi.Models.Today;
using TendEase.DataAccess;

namespace TeBigCoreApi.Controllers
{
    [Produces("application/json")]
    [Route("api/Today")]
    public class TodayController : Controller
    {
        private void OnException(
            CommonResponse resp,
            Exception ex,
            [CallerMemberName] string callerName = ""
        )
        {
            resp.OnException(ex);

            Log.Error($"{callerName}: {ex.Message}");
        }

        [Route("QueryTeam")]
        [HttpPost]
        public QueryTeamResponse QueryTeam([FromBody] QueryTeamRequest req)
        {
            var resp = new QueryTeamResponse();

            try
            {
                string sql_team = "select * from V_LED_DRHWT";
                string sql_operator = "select * from V_LED_JJYHWTJ";

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                {
                    using (var reader = ida.ExecuteReader(sql_operator))
                    {
                        while (reader.Read())
                        {
                            var row = new TeamOperator()
                            {
                                ID = reader.GetStringEx("JJYGH"),
                                Name = reader.GetStringEx("JJYXM"),
                                Team = reader.GetStringEx("SZBZ"),
                                Hrsl = reader.GetInt32Ex("HRSL"),
                                Yxjq = reader.GetInt32Ex("YXJQ"),
                                Fzjj = reader.GetInt32Ex("FZJJ"),
                                Zgsc = reader.GetInt32Ex("ZGSC"),
                                Dxfs = reader.GetInt32Ex("DXFS"),
                                Hrzs = reader.GetInt32Ex("HRZS"),
                                Thgd = reader.GetInt32Ex("DHGD"),
                                Kzcs = reader.GetInt32Ex("KZCS"),
                                Gqcs = reader.GetInt32Ex("GQCS"),
                            };

                            resp.Peoples.Add(row);
                        }
                    }

                    using (var reader = ida.ExecuteReader(sql_team))
                    {
                        if (reader.Read())
                        {
                            resp.Team = new TeamStat()
                            {
                                Hrzl = reader.GetInt32Ex("HRZL"),
                                Yxjl = reader.GetInt32Ex("YXJQ"),
                                Dxfs = reader.GetInt32Ex("DXFS"),
                                Pdzs = reader.GetInt32Ex("PDZS"),
                                Hrzs = reader.GetInt32Ex("HRZS"),
                                Zgsc = reader.GetInt32Ex("ZGSC"),
                                Zgrs = reader.GetInt32Ex("ZGRS"),
                            };
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        [Route("QueryCaseStat")]
        [HttpPost]
        public QueryCaseStatResponse QueryCaseStat([FromBody] QueryCaseStatRequest req)
        {
            var resp = new QueryCaseStatResponse();

            try
            {
                string sql = string.Format(
                    @"
with
  a as (
   select gxdwdm, jqlbdm, count(*) cnt 
   from v_jjdb 
   where bjsj between {0} and {1}
   group by gxdwdm, jqlbdm
  ),
  m as (
   select x.gxdwdm,x.jqlbdm,count(distinct y.jjdbh) cnt
    from v_fkdb y
    left join v_jjdb x on x.jjdbh = y.jjdbh
    where bjsj between {0} and {1}
    group by x.gxdwdm,x.jqlbdm
  ),
  n as
  (
   select x.gxdwdm,x.jqlbdm,count(distinct y.jjdbh) cnt
    from v_pjdb y
    left join v_jjdb x on x.jjdbh = y.jjdbh
    where bjsj between {0} and {1}
    group by x.gxdwdm,x.jqlbdm
  )
select 
  a.gxdwdm     f_dept_code,
  a.jqlbdm     f_type_code,
  c.mc         f_type_name,
  nvl(m.cnt,0) f_feedback,
  nvl(n.cnt,0) f_deal,
  a.cnt        f_count
from a
left join T_DM_AY c on a.jqlbdm = c.dm
left join m on (a.gxdwdm = m.gxdwdm and a.jqlbdm = m.jqlbdm)
left join n on (a.gxdwdm = n.gxdwdm and a.jqlbdm = n.jqlbdm)
",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                );

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var row = new CaseDeptStat()
                        {
                            FDeptCode = reader.GetStringEx("f_dept_code"),
                            FTypeCode = reader.GetStringEx("f_type_code"),
                            FTypeName = reader.GetStringEx("f_type_name"),
                            FFeedback = reader.GetInt32Ex("f_feedback"),
                            FDeal = reader.GetInt32Ex("f_deal"),
                            FCount = reader.GetInt32Ex("f_count"),
                        };

                        resp.List.Add(row);
                    }
                }

                resp.AllStat.AddRange(
                    resp.List.GroupBy(r => r.FTypeCode)
                        .Select(r => new CaseGroupStat
                        {
                            FTypeCode = r.First().FTypeCode,
                            FDisplayName = r.First().FTypeName,
                            FCount = r.Sum(k => k.FCount),
                        })
                        .OrderBy(r => r.FTypeCode)
                );
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        [Route("QueryCaseContent")]
        [HttpPost]
        public QueryCaseContentResponse QueryCaseContent([FromBody] QueryCaseContentRequest req)
        {
            var resp = new QueryCaseContentResponse();

            try
            {
                string sql = string.Format(
                    @"
select 
   a.bjnr       f_content
from v_jjdb a
where bjsj between {0} and {1}
",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                );

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var row = new CaseContent() { FContent = reader.GetStringEx("f_content") };

                        resp.List.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        [Route("QueryCaseXY")]
        [HttpPost]
        public QueryCaseXYResponse QueryCaseXY([FromBody] QueryCaseXYRequest req)
        {
            var resp = new QueryCaseXYResponse();

            try
            {
                string sql = string.Format(
                    @"
select 
   a.bjnr       f_content,
   a.jqlbdm     f_type_code,
   b.gis_x      f_latitude, 
   b.gis_y      f_longitude 
from v_jjdb a
left join v_pgis b on a.JJDBH = b.jjdbh
where bjsj between {0} and {1}
and nvl(gis_x, 0)>1 and nvl(gis_y,0)>1
",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                );

                //                string sql = string.Format(@"
                //select
                //    a.bjnr       f_content,
                //    a.jqlbdm     f_type_code,
                //    a.syxx       f_longitude,
                //    a.zbxx       f_latitude
                //from jjdb a
                //where bjsj between {0} and {1}
                //and nvl(syxx, 0)>1 and nvl(zbxx,0)>1
                //", DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                //DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                //);

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var d1 = reader.GetDoubleEx("f_longitude");
                        var d2 = reader.GetDoubleEx("f_latitude");

                        var row = new CaseXY()
                        {
                            Longitude = Math.Max(d1, d2),
                            Latitude = Math.Min(d1, d2),
                            FContent = reader.GetStringEx("f_content"),
                            FTypeCode = reader.GetStringEx("f_type_code"),
                        };

                        resp.List.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        /// <summary>
        /// 巡逻警力
        /// </summary>
        [Route("QueryPatrolStat")]
        [HttpPost]
        public QueryPatrolStatResponse QueryPatrolStat([FromBody] QueryPatrolStatRequest req)
        {
            var resp = new QueryPatrolStatResponse();

            try
            {
                string sql =
                    @"
with
  t_realtime as (select * from(
     select floor(patrol/100000) pid, k.*
     from MAIN_REALTIME k
     where patrol is not null and patrol > 100000
  ))
select 
 a.f_id   f_area_code,
 a.f_name f_area_name,
 a.f_dept f_dept_code,
 c.name   f_dept_name,
 (select count(count(*)) from t_realtime where pid=a.f_id group by patrol) f_group_count,
 (select count(*) from t_realtime where pid=a.f_id) f_patrol_count,
 (select count(*) from t_realtime where pid=a.f_id and carstate <>8 and carstate<>9) f_left_count
 from PL_PATROL a
left join um_user c on a.f_dept = c.usercode
";

                string sql2 =
                    @"
with
  t_usercode as (
     select distinct PKG_DEPT.SearchBureau(usercode) f_dept
     from MAIN_REALTIME m 
     where floor(patrol/100000) = 9999
  ),
  t_realtime as (
     select PKG_DEPT.SearchBureau(usercode) f_dept, m.*
     from MAIN_REALTIME m 
     where floor(patrol/100000) = 9999
  )
select 
'9999'           f_area_code,
'备勤巡处力量'     f_area_name,
a.f_dept         f_dept_code,
c.name           f_dept_name,
1                f_group_count,
(select count(*) from t_realtime where f_dept=a.f_dept) f_patrol_count,
(select count(*) from t_realtime where f_dept=a.f_dept and carstate <>8 and carstate<>9) f_left_count
from t_usercode a
left join um_user c on a.f_dept = c.usercode

";

                void ReadData(IDataAccess ida, string s)
                {
                    using (var reader = ida.ExecuteReader(s))
                    {
                        while (reader.Read())
                        {
                            var row = new PatrolAreaStat()
                            {
                                FDeptCode = reader.GetStringEx("f_dept_code"),
                                FDeptName = reader.GetStringEx("f_dept_name"),
                                FAreaCode = reader.GetStringEx("f_area_code"),
                                FAreaName = reader.GetStringEx("f_area_name"),
                                FGroupCount = reader.GetInt32Ex("f_group_count"),
                                FPatrolCount = reader.GetInt32Ex("f_patrol_count"),
                                FLeftCount = reader.GetInt32Ex("f_left_count"),
                            };

                            resp.List.Add(row);
                        }
                    }
                }

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.GpsConnConfig))
                {
                    ReadData(ida, sql);

                    ReadData(ida, sql2);
                }

                //resp.List.Add(new PatrolAreaStat()
                //{
                //    FDeptCode = ("f_dept_code"),
                //    FDeptName = ("f_dept_name"),
                //    FAreaCode = ("f_area_code"),
                //    FAreaName = ("f_area_name"),
                //    FGroupCount = 100,
                //    FPatrolCount = 100,
                //    FLeftCount = 100,
                //});

                resp.AreaStat.AddRange(
                    resp.List.Select(r => new PatrolStat
                    {
                        FDisplayName = r.FAreaName,
                        FDisplayText =
                            $"巡逻组:{r.FGroupCount}, 巡逻警力:{r.FPatrolCount}, 剩余警力:{r.FLeftCount}",
                        Reference = r,
                    })
                );

                resp.DeptStat.AddRange(
                    resp.List.GroupBy(r => r.FDeptCode)
                        .Select(r => new PatrolStat
                        {
                            FDisplayName = r.First().FDeptName,
                            FDisplayText =
                                $"巡逻组:{r.Sum(k => k.FGroupCount)}, 巡逻警力:{r.Sum(k => k.FPatrolCount)}, 剩余警力:{r.Sum(k => k.FLeftCount)}",
                        })
                );
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        [Route("QueryCaseReason")]
        [HttpPost]
        public QueryCaseReasonResponse QueryCaseReason([FromBody] QueryCaseReasonRequest req)
        {
            var resp = new QueryCaseReasonResponse();

            try
            {
                string sql = string.Format(
                    @"
with 
 a as (select jqlxdm, count(*) cnt 
    from v_jjdb
    where bjsj between {0} and {1}
    group by jqlxdm)
select 
a.jqlxdm f_reason_code,
b.mc     f_reason_name,
a.cnt    f_count
from a
left join t_dm_ay b on a.jqlxdm=b.dm
",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                );

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var row = new CaseReason()
                        {
                            FReasonCode = reader.GetStringEx("f_reason_code"),
                            FReasonName = reader.GetStringEx("f_reason_name"),
                            FCount = reader.GetInt32Ex("f_count"),
                        };

                        resp.List.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        /// <summary>
        /// 重要案情
        /// </summary>
        [Route("QueryCaseInfo")]
        [HttpPost]
        public QueryCaseInfoResponse QueryCaseInfo([FromBody] QueryCaseInfoRequest req)
        {
            var resp = new QueryCaseInfoResponse();

            try
            {
                string sql = string.Format(
                    @"
select * from v_gzjq a
where bjsj between {0} and {1}",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                );

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var row = new MajorCaseInfo()
                        {
                            FDate = reader.GetDateTimeEx("f_date"),
                            FContent = reader.GetStringEx("f_content"),
                            FDeptCode = reader.GetStringEx("f_dept_code"),
                            FTypeCode = reader.GetStringEx("f_type_code"),
                            FTypeName = reader.GetStringEx("f_type_name"),
                        };

                        resp.List.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        [Route("QueryCaseDetailStat")]
        [HttpPost]
        public QueryCaseDetailStatResponse QueryCaseDetailStat(
            [FromBody] QueryCaseDetailStatRequest req
        )
        {
            var resp = new QueryCaseDetailStatResponse();
            try
            {
                string sql = string.Format(
                    @"
with
  a as (
   select nvl(jqxldm, jqlxdm) f_type_code, count(*) cnt 
   from v_jjdb 
   where bjsj between {0} and {1} and  jqlbdm='{2}'
   group by nvl(jqxldm, jqlxdm)
  )
select 
  a.f_type_code     f_type_code,
  c.mc              f_type_name,
  a.cnt             f_count
from a
left join T_DM_AY c on a.f_type_code = c.dm
",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End),
                    req.TypeCode
                );

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var row = new CaseDetailStat()
                        {
                            FTypeCode = reader.GetStringEx("f_type_code"),
                            FTypeName = reader.GetStringEx("f_type_name"),
                            FCount = reader.GetInt32Ex("f_count"),
                        };

                        resp.List.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }

        [Route("QueryCaseHourStat")]
        [HttpPost]
        public QueryCaseHourStatResponse QueryCaseHourStat([FromBody] QueryCaseHourStatRequest req)
        {
            var resp = new QueryCaseHourStatResponse();

            try
            {
                string sql = string.Format(
                    @"
SELECT 
TRUNC(bjsj, 'HH24') f_datetime, 
count(*) f_count
FROM v_jjdb
where bjsj between {0} and {1}
GROUP BY TRUNC(bjsj, 'HH24')
order by TRUNC(bjsj, 'HH24')
",
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.Begin),
                    DbCommon.DsConnConfig.Provider.GetDateTimeString(req.End)
                );

                using (IDataAccess ida = DataAccessFactory.New(DbCommon.DsConnConfig))
                using (var reader = ida.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var row = new CaseHourStat()
                        {
                            FDateTime = reader.GetDateTimeEx("f_datetime"),
                            FCount = reader.GetInt32Ex("f_count"),
                        };

                        resp.List.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                OnException(resp, ex);
            }

            return resp;
        }
    }
}
